package CustomClassFile.yueli;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.StaticObj;

public class SalarySearch {

	/**
	 * 	查询薪资
	 * @param peid	薪资编号
	 * @param date	日期(必须每月1号:yyyy-MM-01)
	 */
	private static  Map<String ,Object>  searchBypeidAnddate(String peid,String date) {
		java.sql.Connection conn = null; 
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		conn = ds.getConnection();
		Map<String ,Object> map=new HashMap<>();
		map.put("flag", false);
		 try {
			CallableStatement callstat=conn.prepareCall("{call zsws_SP_PayAll(?,?)}");
			callstat.setString(1, peid);
			callstat.setString(2, date);
			ResultSet rs = callstat.executeQuery();
			if (rs.next()) {
				map.put("jsid", rs.getString("jsid"));
				map.put("peid",rs.getString("peid"));
				map.put("tj0",DecimalLength(rs.getString("tj0"),2));
				map.put("shifa",DecimalLength(rs.getString("shifa"),2));
				map.put("tj1",DecimalLength(rs.getString("tj1"),2));
				map.put("tj2",DecimalLength(rs.getString("tj2"),2));
				map.put("tj3",DecimalLength(rs.getString("tj3"),2));
				map.put("tj4",DecimalLength(rs.getString("tj4"),2));
				map.put("tj5",DecimalLength(rs.getString("tj5"),2));
				map.put("tj6",DecimalLength(rs.getString("tj6"),2));
				map.put("tj7",DecimalLength(rs.getString("tj7"),2));
				map.put("tj8",DecimalLength(rs.getString("tj8"),2));
				map.put("tj9",DecimalLength(rs.getString("tj9"),2));
				map.put("flag", true);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		 return map;
	}
	
	public static JSONObject SP_PayAll_fri(String id ,String date){
		String peid=getPeidByid(id);
		JSONObject map=new JSONObject();
		map.put("totalSize", 0);
		map.put("datas", new JSONArray());
		if (peid!=null) {
			java.sql.Connection conn = null; 
			weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
					.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
			conn = ds.getConnection();
			 try {
				CallableStatement callstat=conn.prepareCall("{call zsws_SP_PayAll_fri(?,?)}");
				callstat.setString(1, peid);
				callstat.setString(2, date);
				ResultSet rs = callstat.executeQuery();
				JSONArray listmap = new JSONArray();
				int totalsize=0;
				while(rs.next()) {
					JSONObject mapindex = new JSONObject();
					mapindex.put("jsid", rs.getString("jsid"));
					mapindex.put("peid",rs.getString("peid"));
					mapindex.put("xvalue",DecimalLength(rs.getString("xvalue"),2));
					mapindex.put("title",rs.getString("title"));
					listmap.add(mapindex);
					totalsize++;
				}
				map.put("totalSize", totalsize);
				map.put("datas", listmap);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}
	public static JSONObject SP_PayAll_sec(String id ,String date){
		String peid=getPeidByid(id);
		JSONObject map=new JSONObject();
		map.put("totalSize", 0);
		map.put("datas", new JSONArray());
		if (peid!=null) {
			java.sql.Connection conn = null; 
			weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
					.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
			conn = ds.getConnection();
			 try {
				CallableStatement callstat=conn.prepareCall("{call zsws_SP_PayAll_sec(?,?)}");
				callstat.setString(1, peid);
				callstat.setString(2, date);
				ResultSet rs = callstat.executeQuery();
				JSONArray listmap = new JSONArray();
				int totalsize=0;
				while(rs.next()) {
					JSONObject mapindex = new JSONObject();
					mapindex.put("jsid", rs.getString("jsid"));
					mapindex.put("peid",rs.getString("peid"));
					mapindex.put("xvalue",DecimalLength(rs.getString("xvalue"),2));
					mapindex.put("title",rs.getString("title"));
					listmap.add(mapindex);
					totalsize++;
				}
				map.put("totalSize", totalsize);
				map.put("datas", listmap);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}
	//查询第一项明细
	public static JSONObject SP_PayAll_Tj1(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj1");
		return map;
	}
	public static JSONObject SP_PayAll_Tj2(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj2");
		return map;
	}
	public static JSONObject SP_PayAll_Tj3(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj3");
		return map;
	}
	public static JSONObject SP_PayAll_Tj4(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj4");
		return map;
	}
	public static JSONObject SP_PayAll_Tj5(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj5");
		return map;
	}
	public static JSONObject SP_PayAll_Tj6(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj6");
		return map;
	}
	public static JSONObject SP_PayAll_Tj7(String id ,String date){
		String peid=getPeidByid(id);
		JSONObject map=new JSONObject();
		map.put("totalSize", 0);
		map.put("datas", new JSONArray());
		if (peid!=null) {
			java.sql.Connection conn = null; 
			weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
					.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
			conn = ds.getConnection();
			 try {
				CallableStatement callstat=conn.prepareCall("{call zsws_SP_PayAll_Tj7(?,?)}");
				callstat.setString(1, peid);
				callstat.setString(2, date);
				ResultSet rs = callstat.executeQuery();
				JSONArray listmap = new JSONArray();
				int totalsize=0;
				while(rs.next()) {
					JSONObject mapindex = new JSONObject();
					mapindex.put("jsid", rs.getString("jsid"));
					mapindex.put("peid",rs.getString("peid"));
					mapindex.put("xvalue",rs.getString("xvalue"));
					mapindex.put("title",rs.getString("title"));
					listmap.add(mapindex);
					totalsize++;
				}
				map.put("totalSize", totalsize);
				map.put("datas", listmap);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}
	public static JSONObject SP_PayAll_Tj8(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj8");
		return map;
	}
	public static JSONObject SP_PayAll_Tj9(String id ,String date){
		JSONObject map=SP_PayAll_TjNum(id, date, "zsws_SP_PayAll_Tj9");
		return map;
	}
	
	
	
	/**
	 * 	查询peid
	 * @param id
	 * @return
	 */
	private static String getPeidByid(String id) {
		String peid=null;
		RecordSet res=new RecordSet();
		String sql="select field1 as peid from cus_fielddata where id=?";
		res.executeQuery(sql, id);
		if (res.next()) {
			peid=res.getString("peid");
		}
		return peid;
	}
	
	public static Map<String ,Object> getSalary(String id,String date){
		String peid=getPeidByid(id);
		Map<String ,Object> map=new HashMap<>();
		map.put("flag", false);
		if (peid!=null) {
			map=searchBypeidAnddate(peid, date);
		}
		return map;
	}
	
	public static String DecimalLength(String number,int lenth) {
		BigDecimal bigd=new BigDecimal(number);
		bigd=bigd.setScale(lenth, BigDecimal.ROUND_DOWN);
		return bigd.toString();
	}
	
	/**
	 * 查询应加应扣
	 * @return
	 */
	public static JSONObject getType2(String id,String date){
		
		// 查询eid
		String sqleid = "select lastname,outkey from hrmresource where id=?";
		String eid = "";
		RecordSet rseid = new RecordSet();
		rseid.executeQuery(sqleid, id);
		if (rseid.next()) {
			eid = rseid.getString("outkey");
		}
		
		int totalsize=0;
		JSONArray dataArray = new JSONArray();		
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		try {
			String sql = "select * from zswsHRadjust_all where eid=? and convert(date,term)=?";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, eid);
			preparedStatement.setString(2, date);
			ResultSet rs = preparedStatement.executeQuery();
			while(rs.next()) {
				String amout=rs.getString("amount");
				String title=rs.getString("title");
				String remark=rs.getString("remark");
				JSONObject jsonObj = new JSONObject();
				jsonObj.put("amount", amout);
				jsonObj.put("title", title);
				jsonObj.put("remark", remark);
				dataArray.add(jsonObj);
				totalsize++;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JSONObject resultObj = new JSONObject();
		resultObj.put("totalSize", totalsize);
		resultObj.put("datas", dataArray);
		return resultObj;
	}
	
	
	public static JSONObject SP_PayAll_TjNum(String id ,String date,String num){
		String peid=getPeidByid(id);
		JSONObject map=new JSONObject();
		map.put("totalSize", 0);
		map.put("datas", new JSONArray());
		if (peid!=null) {
			java.sql.Connection conn = null; 
			weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
					.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
			conn = ds.getConnection();
			 try {
				CallableStatement callstat=conn.prepareCall("{call "+num+"(?,?)}");
				callstat.setString(1, peid);
				callstat.setString(2, date);
				ResultSet rs = callstat.executeQuery();
				JSONArray listmap = new JSONArray();
				int totalsize=0;
				while(rs.next()) {
					JSONObject mapindex = new JSONObject();
					mapindex.put("jsid", rs.getString("jsid"));
					mapindex.put("peid",rs.getString("peid"));
					mapindex.put("xvalue",DecimalLength(rs.getString("xvalue"),2));
					mapindex.put("title",rs.getString("title"));
					listmap.add(mapindex);
					totalsize++;
				}
				map.put("totalSize", totalsize);
				map.put("datas", listmap);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}
}
